﻿//  -------------------- iSharePoint -------------------- //
// 
//  Company:	IT Complex, LLC
//  Project:	Smart.Common
//  File name:	SqlListService.cs
//  Developer:	Solomatov Igor
//  Created:	15.04.2012
// 
//  -------------------- iSharePoint -------------------- //

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace Smart.Common.Entities
{
	/// <summary>
	/// SQL List Service
	/// </summary>
	public class SqlListService<TEntityType> : ListService<TEntityType>
		where TEntityType : Entity
	{
		/// <summary>
		/// SQL List Service
		/// </summary>
		public SqlListService(IEntityScope ctx, string listName, IEntityMapper mapper, MetaList meta)
			: base(ctx, listName, mapper, meta)
		{
		}

		/// <summary>
		/// Create item in data source
		/// </summary>
		/// <returns>Item</returns>
		protected override object CreateItem()
		{
			return new SqlItem();
		}

		/// <summary>
		/// Loads item from data source
		/// </summary>
		/// <param name="id">Item ID</param>
		/// <returns>Item</returns>
		protected virtual void LoadItem(Identifier id, SqlItem item)
		{
			if (id.IsEmpty)
				throw new ArgumentException("id");

			item.ID = Identifier.Empty;
			item.Values.Clear();

			var so = (MetaSqlObject)Meta;

			//if (!(so.Fields.Count == 1 && so.Field(so.PrimaryKey) != null))
			//    throw new NotSupportedException();

			var cmd = (SqlCommand)Ctx.GetSource(Meta);
			var conn = cmd.Connection;

			using (conn)
			using (cmd)
			{
				if (so.ObjectType == SqlObjectType.SP)
				{
					cmd.CommandText = so.Name;
					cmd.CommandType = CommandType.StoredProcedure;

					cmd.Parameters.Add(new SqlParameter("@" + so.PrimaryKey, id.Key));
				}
				else
				{
					cmd.CommandText = string.Format(SqlConst.QueryObjectById, so.Name, so.PrimaryKey, id.Key.IncapsulateSql());
				}

				cmd.CommandTimeout = SqlConst.FastCommandTimeout;

				using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
				{
					if (reader.Read())
					{
						item.Load(reader, SqlItem.LoadMap(reader));
						item.MapSystemFields(so.PrimaryKey, so.TitleKey);
					}
				}
			}
		}

		/// <summary>
		/// Loads item from data source
		/// </summary>
		/// <param name="id">Item ID</param>
		/// <returns>Item</returns>
		protected override object LoadItem(Identifier id)
		{
			var item = new SqlItem(id);

			LoadItem(id, item);

			return item.ID.IsEmpty ? null : item;
			/*
			if (id.IsEmpty)
				throw new ArgumentException("id");

			var so = (MetaSqlObject)Meta;

			//if (!(so.Fields.Count == 1 && so.Field(so.PrimaryKey) != null))
			//    throw new NotSupportedException();

			var cmd = (SqlCommand)Ctx.GetSource(Meta);
			var conn = cmd.Connection;

			using (conn)
			using (cmd)
			{
				if (so.ObjectType == SqlObjectType.SP)
				{
					cmd.CommandText = so.Name;
					cmd.CommandType = CommandType.StoredProcedure;

					cmd.Parameters.Add(new SqlParameter("@" + so.PrimaryKey, id.Key));
				}
				else
				{
					cmd.CommandText = string.Format(SqlConst.QueryObjectById, so.Name, so.PrimaryKey, H.IncapsulateSql(id.Key));
				}

				cmd.CommandTimeout = SqlConst.FastCommandTimeout;

				using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
				{
					if (reader.Read())
					{
						var res = new SqlItem(id);

						res.Load(reader, SqlItem.LoadMap(reader));

						res.MapSystemFields(so.PrimaryKey, so.TitleKey);

						return res;
					}
				}
			}

			return null;
			 */
		}

		/*
		/// <summary>
		/// Save entity values in item
		/// </summary>
		/// <param name="item">Item to save to</param>
		/// <param name="entity">Entity to save from</param>
		/// <returns>true, if succeeded</returns>
		public override bool SaveEntity(object item, TEntityType entity)
		{
			if (entity == null || item == null)
				throw new ArgumentNullException();

			var entitySaved = EntityFactory.SaveEntity(item, entity, Mapper);

			var reloadedItem = SaveAndReloadItem(entity.ID, ref item, entity.IsNew);

			var entityReloaded = LoadEntity(entity, item);

			return entitySaved && reloadedItem && entityReloaded;
		}
		 */

		/// <summary>
		/// Save item to data source
		/// </summary>
		/// <param name="id">Item ID</param>
		/// <param name="item">Item</param>
		/// <returns>true if succeeded</returns>
		protected override bool SaveItem(Identifier id, object item, bool isNew)
		{
			return SaveAndReloadItem(id, item, isNew);
		}

		/// <summary>
		/// Save item to data source and reload updated values
		/// </summary>
		/// <param name="id">Item ID</param>
		/// <param name="item">Item</param>
		/// <returns>not null object if succeeded</returns>
		protected bool SaveAndReloadItem(Identifier id, object item, bool isNew)
		{
			if (item == null)
				throw new ArgumentNullException("item");

			var so = (MetaSqlObject)Meta;
			var sqlitem = (SqlItem)item;

			sqlitem.UnmapSystemFields(so.PrimaryKey, so.TitleKey);

			var commandText = string.Empty;
			var sqlParameterCollection = new Dictionary<string, object>();

			if (isNew)
			{
				if (!so.SPAdd.IsNullOrEmpty())
				{
					return ExecuteSaveAndReloadCommand(so.SPAdd, CommandType.StoredProcedure, item, so.SPAddParameters);
				}

				if (so.ObjectType != SqlObjectType.Table)
					throw new NotSupportedException(string.Format("Impossible to save item ({0}) for {1}. ObjectType is not Table, but no Add SP provided.", id, Meta.Name));

				// code for Table mode
				var sbC = new StringBuilder();
				var sbV = new StringBuilder();

				foreach (var value in sqlitem.Values)
				{
					var field = so.Field(value.Key) as MetaSqlColumn;
					// need to remove field if identity or if computed
					if (field != null && field.IsComputed || field.IsIdentity) continue;

					var param = "@" + value.Key;

					sbC.Append(value.Key + ", ");
					sbV.Append(param + ", ");

					sqlParameterCollection.Add(param, value.Value);
				}

				var primaryKeyField = so.Field(so.PrimaryKey) as MetaSqlColumn;
				if (primaryKeyField.IsIdentity)
				{
					commandText = string.Format(SqlConst.InsertItemIdentity, so.Name,
												sbC.Remove(sbC.Length - 2, 2), sbV.Remove(sbV.Length - 2, 2), so.PrimaryKey);
				}
				else
				{
					commandText = string.Format(SqlConst.InsertItemStandart, so.Name,
												sbC.Remove(sbC.Length - 2, 2), sbV.Remove(sbV.Length - 2, 2), so.PrimaryKey, id.Key.IncapsulateSql());
				}
			}
			else //existing item - need to update
			{
				if (!so.SPUpdate.IsNullOrEmpty())
				{
					return ExecuteSaveAndReloadCommand(so.SPUpdate, CommandType.StoredProcedure, item, so.SPUpdateParameters);
				}

				if (so.ObjectType != SqlObjectType.Table)
					throw new NotSupportedException(string.Format("Impossible to save item ({0}) for {1}. ObjectType is not Table, but no Update SP provided.", id, Meta.Name));

				// code for Table mode
				var fieldSetters = new StringBuilder();

				foreach (var value in sqlitem.Values)
				{
					var field = so.Field(value.Key) as MetaSqlColumn;
					// need to remove field if identity or if computed
					if (field != null && field.IsComputed || field.IsIdentity) continue;

					var param = "@" + value.Key;

					sqlParameterCollection.Add(param, value.Value);

					fieldSetters.Append(value.Key + "=" + param + ", ");
				}

				commandText = string.Format(SqlConst.UpdateItem, so.Name, fieldSetters.Remove(fieldSetters.Length - 2, 2), so.PrimaryKey, id.Key.IncapsulateSql());
			}

			return ExecuteSaveAndReloadCommand(commandText, CommandType.Text, item, sqlParameterCollection);
		}

		/// <summary>
		/// Execute save item and reload item command
		/// </summary>
		/// <param name="commandText">Command text</param>
		/// <param name="item">Item to save and reload</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>True if ok</returns>
		protected bool ExecuteSaveAndReloadCommand(string commandText, CommandType type, object item, Dictionary<string, object> parameters)
		{
			var result = ExecuteCommand(commandText, type, parameters);
			var so = (MetaSqlObject)Meta;

			if (result != null && result.Values.ContainsKey(so.ResultCodeKey))
			{
				var resultCode = result.Values.SafeGetInt(so.ResultCodeKey, 0);
				var description = result.Values.SafeGetStr(so.ResultDescriptionKey, null);
				var idv = result.Values.SafeGetLoad(so.PrimaryKey, null);
				var id = idv != null ? new Identifier(idv) : ((SqlItem)item).ID;

				if (resultCode == 0)
					throw new Exception(description);

				LoadItem(id, (SqlItem)item);

				return (item != null);
			}

			return false;
		}

		/// <summary>
		/// Execute save item and reload item command
		/// </summary>
		/// <param name="commandText">Command text</param>
		/// <param name="item">Item to save and reload</param>
		/// <param name="columns">Command parameters</param>
		/// <returns>True if ok</returns>
		protected bool ExecuteSaveAndReloadCommand(string commandText, CommandType type, object item, List<MetaSqlColumn> columns)
		{
			var itemP = (SqlItem)item;
			var parameters = columns.ToDictionary(column => column.Name, column => itemP.Values.SafeGet(column.Name), StringComparer.OrdinalIgnoreCase);

			return ExecuteSaveAndReloadCommand(commandText, type, item, parameters);
		}

		/// <summary>
		/// Execute SQL command
		/// </summary>
		/// <param name="commandText">Command text</param>
		/// <param name="parameters">SQL Command parameters</param>
		/// <returns>Result Item</returns>
		public SqlItem ExecuteCommand(string commandText, CommandType type, Dictionary<string, object> parameters)
		{
			if (commandText.IsNullOrEmpty())
				throw new ArgumentNullException("commandText");

			var so = (MetaSqlObject)Meta;

			var cmd = (SqlCommand)Ctx.GetSource(Meta);
			var conn = cmd.Connection;

			using (conn)
			using (cmd)
			{
				cmd.CommandText = commandText;
				cmd.CommandTimeout = SqlConst.NormalCommandTimeout;
				cmd.CommandType = type;

				foreach (var param in parameters)
				{
					cmd.Parameters.Add(new SqlParameter("@" + param.Key.Trim(), param.Value ?? DBNull.Value));
				}

				using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow))
				{
					if (reader.Read())
					{
						var res = new SqlItem();

						res.Load(reader, SqlItem.LoadMap(reader));
						//res.MapSystemFields(so.PrimaryKey, so.TitleKey);

						return res;
					}
				}
			}

			return null;
		}

		/// <summary>
		/// Execute Delete command to delete item in data source
		/// </summary>
		/// <param name="commandText">Command text</param>
		/// <param name="id">Id of item to delete</param>
		/// <returns>true if ok</returns>
		protected bool ExecuteDeleteCommand(string commandText, CommandType type, Dictionary<string, object> parameters)
		{
			//var parameters = new Dictionary<string, object>();

			var so = (MetaSqlObject)Meta;
			//parameters.Add(so.PrimaryKey, id.Key);

			var result = ExecuteCommand(commandText, type, parameters);

			if (result != null && result.Values.ContainsKey(so.ResultCodeKey))
			{
				var resultCode = result.Values[so.ResultCodeKey].SToInt();
				var description = result.Values[so.ResultDescriptionKey].SToStr();

				if (resultCode == 0)
					throw new Exception(description);
			}

			return false;
		}

		/// <summary>
		/// Recycle item (move to recycle bin) in data source
		/// </summary>
		/// <param name="id">Item ID</param>
		/// <returns>true, if succeeded</returns>
		protected override bool RecycleItem(Identifier id)
		{
			if (id.IsEmpty)
				throw new ArgumentException("id");

			var so = (MetaSqlObject)Meta;
			if (so.ObjectType != SqlObjectType.Table)
				throw new NotSupportedException(string.Format("Impossible to delete item ({0}) for {1}. ObjectType is not Table, but no Delete SP provided.", id, Meta.Name));

			var parameters = new Dictionary<string, object>();
			parameters.Add(so.PrimaryKey, id.Key);

			return ExecuteDeleteCommand(string.Format(SqlConst.DeleteObjectById, so.Name, so.PrimaryKey, "@" + so.PrimaryKey), CommandType.Text, parameters);

			//throw new NotSupportedException("Delete only by id is not supported.");
		}

		/// <summary>
		/// Recycle item (move to recycle bin) in data source
		/// </summary>
		/// <param name="item">Item</param>
		/// <returns>true, if succeeded</returns>
		protected override bool RecycleItem(object item)
		{
			if (item == null)
				throw new ArgumentException("item");

			var so = (MetaSqlObject)Meta;
			var SqlEntity = (SqlEntity)item;
			var sqlitem = (SqlItem)SqlEntity.Source;

			sqlitem.UnmapSystemFields(so.PrimaryKey, so.TitleKey);

			var parameters = so.SPDeleteParameters.ToDictionary(column => column.Name, column => sqlitem.Values[column.Name], StringComparer.OrdinalIgnoreCase);

			if (!so.SPDelete.IsNullOrEmpty())
			{
				return ExecuteDeleteCommand(so.SPDelete, CommandType.StoredProcedure, parameters);
			}

			if (so.ObjectType != SqlObjectType.Table)
				throw new NotSupportedException(string.Format("Impossible to delete item ({0}) for {1}. ObjectType is not Table, but no Delete SP provided.", sqlitem.ID, Meta.Name));

			var parameters2 = new Dictionary<string, object>();
			parameters2.Add(so.PrimaryKey, sqlitem.ID.Key);

			return ExecuteDeleteCommand(string.Format(SqlConst.DeleteObjectById, so.Name, so.PrimaryKey, "@" + so.PrimaryKey), CommandType.Text, parameters2);

			//return RecycleItem(((SqlItem)item).ID);
		}

		public override bool RecycleEntity(TEntityType entity)
		{
			return base.RecycleEntity(entity, entity);
		}

		/// <summary>
		/// Loads items using query
		/// </summary>
		/// <param name="query">Data source query</param>
		/// <param name="rowLimit">Row limit</param>
		/// <returns>Items</returns>
		public virtual IEntityList<TEntityType> LoadEntities(Dictionary<string, object> parameters, int rowLimit)
		{
			if (parameters == null)
				throw new ArgumentNullException("parameters");

			var source = LoadItems(parameters, rowLimit);

			if (source == null)
				throw new InvalidDataException();

			var list = new EntityList<TEntityType>(Meta, this);
			var ilist = (IEntityList)list;

			foreach (var item in source)
			{
				var ei = DetectEntityInfo(item);

				if (ei == null)
					continue;

				var entity = CreateEntity(ei.Type, ilist);

				entity.ID = GetId(item);

				if (LoadEntity(entity, item))
				{
					list.Add(entity);
				}
			}

			return list;
		}

		/// <summary>
		/// Loads items from data source using stored procedure
		/// </summary>
		/// <param name="query">Params</param>
		/// <param name="rowLimit">Row limit</param>
		/// <returns>Items</returns>
		protected virtual IEnumerable LoadItems(Dictionary<string, object> parameters, int rowLimit)
		{
			if (parameters == null)
				throw new ArgumentNullException("parameters");

			var so = (MetaSqlObject)Meta;

			if (so.ObjectType != SqlObjectType.SP)
				throw new NotSupportedException("so.ObjectType");

			var hs = new HashSet<string>(parameters.Keys, StringComparer.OrdinalIgnoreCase);

			if (!hs.SetEquals(so.Fields.Values.Select(mf => mf.Name)))
				throw new NotSupportedException("Fields");

			var items = new List<SqlItem>();

			var cmd = (SqlCommand)Ctx.GetSource(Meta);
			var conn = cmd.Connection;

			using (conn)
			using (cmd)
			{
				cmd.CommandText = so.Name;
				cmd.CommandTimeout = SqlConst.NormalCommandTimeout;

				foreach (var item in parameters)
				{
					cmd.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
				}

				using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
				{
					var map = SqlItem.LoadMap(reader);

					while (reader.Read())
					{
						var sqlitem = new SqlItem();

						sqlitem.Load(reader, map);
						sqlitem.MapSystemFields(so.PrimaryKey, so.TitleKey);

						items.Add(sqlitem);
					}
				}
			}

			return items;
		}

		/// <summary>
		/// Loads items from data source using query (to table or view)
		/// </summary>
		/// <param name="query">Data source query</param>
		/// <param name="rowLimit">Row limit</param>
		/// <returns>Items</returns>
		protected override IEnumerable LoadItems(string query, int rowLimit, Dictionary<string, object> properties)
		{
			if (query.IsNullOrEmpty())
				throw new ArgumentException("query");

			var so = (MetaSqlObject)Meta;

			var items = new List<SqlItem>();

			var cmd = (SqlCommand)Ctx.GetSource(Meta);
			var conn = cmd.Connection;

			using (conn)
			using (cmd)
			{
				cmd.CommandText = query;
				cmd.CommandTimeout = SqlConst.NormalCommandTimeout;

				using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
				{
					var map = SqlItem.LoadMap(reader);

					while (reader.Read())
					{
						var sqlitem = new SqlItem();

						sqlitem.Load(reader, map);
						sqlitem.MapSystemFields(so.PrimaryKey, so.TitleKey);

						items.Add(sqlitem);
					}
				}
			}

			return items;
		}

		/// <summary>
		/// Detects entity name from data source item
		/// </summary>
		/// <param name="item">Item</param>
		/// <returns>Entity information</returns>
		public override EntityInfo DetectEntityInfo(object item)
		{
			if (item == null)
				throw new ArgumentNullException("item");

			return EntityFactory.QueryEntity(DefaultEntityType);
		}

		/// <summary>
		/// Return item identifier
		/// </summary>
		/// <param name="item">Item</param>
		protected override Identifier GetId(object item)
		{
			if (item == null)
				throw new ArgumentNullException("item");

			return ((SqlItem)item).ID;
		}
	}
}